A Framework for Re-Optimization of Repetitive Queries

Optimizing executions of queries is the ultimate goal of the query optimizer. Unfortunately, due to the complexities of queries, accuracy of statistics, validities of assumptions, etc., query optimizers often cannot find the best execution plans in their search spaces, conveniently called the optimal plans, for the queries. In this paper, we develop a comprehensive framework for re-optimization of a large and useful set of queries, called repetitive queries. Repetitive queries refer to those queries that are likely to be used repeatedly or frequently in the future. They are usually stored in the database for convenient reuses for the long term. They deserve more optimization efforts than ordinary ad hoc queries. In this research, we identify statistics, called sufficient statistics, that are sufficient to compute the exact intermediate results of all plans of a query. The finding of the sufficient statistics makes it entirely possible for an optimizer to find the truly best plan in its search space for a query. We present two innovative techniques to conduct re-optimization, an eager and a lazy re-optimization. The eager approach gathers all the sufficient statistics at once and generates the best plan. The lazy approach gathers only the statistics that are needed to correct large estimation errors found in the plan and generates a revised plan. We further adapt the two basic techniques to constantly changing database environments by continuously monitoring and revising the plans, called adaptive re-optimization. The adaptive reoptimization is devised to detect and remedy potential suboptimality in the plans in a timely manner for the entire lifetime of the query. We have also designed an approximate re-optimization method that provides an efficient and effective alternative to refining query execution plans. Our work realizes the promise made by the query optimizers, namely, executing queries in the optimal fashions, at least for the repetitive queries.